﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace 智信科技物流系统
{
    class Dao
    {
        static SqlHelper op = new SqlHelper();
        public void GetLogistics(string cmd)
        {
            int flag = 0;
            DataTable dt = new DataTable();

            //读取数据出错自动重试设定
            while (flag >= 0)
            {
                try
                {
                    dt = op.SqlSelect(cmd);
                    flag = -10;
                }
                catch
                {
                    if (flag > 10)
                    {
                        if (MessageBox.Show("服务器连接中断，请尝试重新连接，或者联系客服.", "错误",
                            MessageBoxButtons.RetryCancel) == DialogResult.Retry)
                        {
                            flag = 0;
                        }
                        else
                        {
                            MessageBox.Show("数据更新失败");
                            flag = -1;
                        }
                    }
                    flag++;
                }
            }
            //end

            //对数据进行处理
            int sum = dt.Rows.Count;
            if (sum < 1) return;//没有数据则返回，不进行处理
            //对数据进行一条条的设定

            for (int i = 0; i < sum; i++)
            {
                oneLogistics one = new oneLogistics();
                one.Id = dt.Rows[i][one.Id].ToString();
                one.InformationType1 = dt.Rows[i][one.InformationType1].ToString();
                one.ReleaseTo1 = dt.Rows[i][one.ReleaseTo1].ToString();
                one.DepartureCity1 = dt.Rows[i][one.DepartureCity1].ToString();
                one.ReachCity1 = dt.Rows[i][one.ReachCity1].ToString();
                one.InformationContent1 = dt.Rows[i][one.InformationContent1].ToString();
                one.MobilePhone1 = dt.Rows[i][one.MobilePhone1].ToString();
                one.FixedTelephone1 = dt.Rows[i][one.FixedTelephone1].ToString();
                one.Fax1 = dt.Rows[i][one.Fax1].ToString();
                one.QQ1 = dt.Rows[i][one.QQ1].ToString();
                one.AutoRetransmission1 = dt.Rows[i][one.AutoRetransmission1].ToString();
                one.UserName1 = dt.Rows[i][one.UserName1].ToString();
                one.DepartureTime1 = dt.Rows[i][one.DepartureTime1].ToString();

                //判断是否全部赋值，即数据的完整性
                //if (one.num[0] == 13)
                //{
                Logistics.list.Add(one);
                //log.datatable.Rows.Add(one.Id.ToString(),one.InformationType1,one.ReleaseTo1,
                //    one.DepartureCity1, one.ReachCity1, one.InformationContent1, one.MobilePhone1,
                //    one.FixedTelephone1, one.Fax1, one.QQ1, one.AutoRetransmission1, one.UserName1, one.DepartureCity1);
                //}
                //else
                //{
                //    MessageBox.Show("发现 ID:" + one.Id + " 用户名："+one.UserName1+"该条数据信息不完整\n该条信息将不会被显示，如有需要，可联系客服");
                //}
            }

        }

        public void GetLogistics(List<oneLogistics> list, string cmd)
        {
            int flag = 0;
            DataTable dt = new DataTable();

            //读取数据出错自动重试设定
            while (flag >= 0)
            {
                try
                {
                    dt = op.SqlSelect(cmd);
                    flag = -10;
                }
                catch
                {
                    if (flag > 10)
                    {
                        if (MessageBox.Show("服务器连接中断，请尝试重新连接，或者联系客服.", "错误",
                            MessageBoxButtons.RetryCancel) == DialogResult.Retry)
                        {
                            flag = 0;
                        }
                        else
                        {
                            MessageBox.Show("数据更新失败");
                            flag = -1;
                        }
                    }
                    flag++;
                }
            }
            //end

            //对数据进行处理
            int sum = dt.Rows.Count;
            if (sum < 1) return;//没有数据则返回，不进行处理
            //对数据进行一条条的设定
            for (int i = 0; i < sum; i++)
            {
                oneLogistics one = new oneLogistics();
                one.Id = dt.Rows[i][one.Id].ToString();
                one.InformationType1 = dt.Rows[i][one.InformationType1].ToString();
                one.ReleaseTo1 = dt.Rows[i][one.ReleaseTo1].ToString();
                one.DepartureCity1 = dt.Rows[i][one.DepartureCity1].ToString();
                one.ReachCity1 = dt.Rows[i][one.ReachCity1].ToString();
                one.InformationContent1 = dt.Rows[i][one.InformationContent1].ToString();
                one.MobilePhone1 = dt.Rows[i][one.MobilePhone1].ToString();
                one.FixedTelephone1 = dt.Rows[i][one.FixedTelephone1].ToString();
                one.Fax1 = dt.Rows[i][one.Fax1].ToString();
                one.QQ1 = dt.Rows[i][one.QQ1].ToString();
                one.AutoRetransmission1 = dt.Rows[i][one.AutoRetransmission1].ToString();
                one.UserName1 = dt.Rows[i][one.UserName1].ToString();
                one.DepartureTime1 = dt.Rows[i][one.DepartureTime1].ToString();

                //判断是否全部赋值，即数据的完整性
                //if (one.num[0] == 13)
                //{
                list.Add(one);
                //    }
                //    else
                //    {
                //        MessageBox.Show("发现 ID:" + one.Id + " 用户名：" + one.UserName1 + "该条数据信息不完整\n该条信息将不会被显示，如有需要，可联系客服");
                //    }
            }

        }

        /// <summary>
        /// 绑定主datagridview
        /// </summary>
        /// <param name="dgv">主datagridview</param>
        /// <param name="t">获取指定天数的信息</param>
        ///  public void BindMainDataGridView(DataGridView dgv,int t,ProgressBar pro,Label lab)
        public void BindDataGridView(DataGridView dgv, int t) //计时 主窗体里的datagridview
        {
            List<oneLogistics> log = Logistics.list;//获取数据
            Dao dao = new Dao();
            DateTime time = DateTime.Now.Date;
            t -= 1;//一天以内的则t应该等于零
            time = time.AddDays(-t);//获取九天以内的全部信息
            //  lab.Text = "在正加载数据";
            something.loaded = false;
            Application.DoEvents();
            for (int k = 0; k < log.Count; k++)
            {
                if (Convert.ToDateTime(log[k].DepartureTime1) <= time) continue;//去掉时间不符合的
                if (something.loaded) return;//有新的线程则终止过去的线程
                dgv.Rows.Add(dao.Deal(dgv, log[k]));//添加一行数据           
                //      pro.Value += 1;             
                Application.DoEvents();
            }
            //   lab.Text = "加载完成";
            something.loaded = true;
            /*
           if (dgv.Rows.Count < 1)
           {
               dgv.Rows.Add("当前没有数据");
               dgv.Rows[0].DefaultCellStyle.ForeColor = Color.Red;
 
           }*/

        }
        public void BindDataGridView(DataGridView dgv, List<oneLogistics> log) //绑定指定log到指定datagridview
        {
        
            Dao dao = new Dao();
            for (int k = 0; k < log.Count; k++)
            {
                dgv.Rows.Add(dao.Deal(dgv, log[k]));//添加一行数据           
            }

        }

        public DataTable sqlSelect(string cmd)//单纯的查询语句
        {
            int flag = 0;
            DataTable dt = new DataTable();
            //读取数据出错自动重试设定
            while (flag >= 0)
            {
                try
                {
                    dt = op.SqlSelect(cmd);
                    flag = -10;
                }
                catch
                {
                    if (flag > 10)
                    {
                        if (MessageBox.Show("服务器连接中断，请尝试重新连接，或者联系客服.", "错误",
                            MessageBoxButtons.RetryCancel) == DialogResult.Retry)
                        {
                            flag = 0;
                        }
                        else
                        {
                            MessageBox.Show("数据更新失败");
                            flag = -1;
                        }
                    }
                    flag++;
                }
            }
            //end
            return dt;
        }
        public int sqlInsert(string cmd,string tips)//单纯的插入语句
        {
            int flag = 0;
            int i = 0;
            //读取数据出错自动重试设定
            while (flag >= 0)
            {
                try
                {
                    i = op.SqlNomQuery(cmd);
                    flag = -10;
                }
                catch
                {
                    if (flag > 10)
                    {
                        if (MessageBox.Show("服务器连接中断，请尝试重新连接，或者联系客服.", "错误",
                            MessageBoxButtons.RetryCancel) == DialogResult.Retry)
                        {
                            flag = 0;
                        }
                        else
                        {
                            MessageBox.Show(tips);
                            flag = -1;
                        }
                    }
                    flag++;
                }
            }
            //end

            return i;
        }

        /*暂时用不到了
        public int GetNum(int t)
        {
            DateTime time = DateTime.Now.Date;
            t -= 1;//一天以内的则t应该等于零
            time = time.AddDays(-t);//获取九天以内的全部信息
            int num=0;
            for (int k = 0; k <Logistics.list.Count; k++)
            {
                if (Convert.ToDateTime(Logistics.list[k].DepartureTime1) <= time) continue;//去掉时间不符合的
                num++;
            }
            return num;
        }*/
        /// <summary>
        /// 对一个row进行处理
        /// </summary>
        /// <param name="dgv">模板</param>
        /// <param name="log">处理的数据</param>
        /// <returns></returns>
        public DataGridViewRow Deal(DataGridView dgv, oneLogistics log)
        {
            DataGridViewRow row = new DataGridViewRow();
            //处理ID为显示的格式
            string s = log.DepartureCity1;
            s = s.Substring(s.IndexOf(',') + 1);//分割到达城市
            DateTime temp = Convert.ToDateTime(log.DepartureTime1);//将数据转换成时间的类
            string month = temp.Month.ToString();
            string day = temp.Day.ToString();
            //对时间进行显示上的格式的处理
            if (month.Length < 2)
                month = "0" + month;
            if (day.Length < 2)
                day = "0" + day;
            string stemp = month + day;

            row.CreateCells(dgv, log.Id, stemp + log.Id, s, log.InformationContent1);


            //对每行数据进行变色处理
            if (log.InformationType1.Equals("即时货源"))
            {
                row.DefaultCellStyle.ForeColor = Color.Red;
            }
            else
            {
                row.DefaultCellStyle.ForeColor = Color.Blue;
            }
            return row;

        }

   
        public static DateTime DataStandardTime()//返回国际标准时间
        {
            //只使用的时间服务器的IP地址，未使用域名
            string[,] 时间服务器 = new string[14, 2];
            int[] 搜索顺序 = new int[] { 3, 2, 4, 8, 9, 6, 11, 5, 10, 0, 1, 7, 12 };
            时间服务器[0, 0] = "time-a.nist.gov";
            时间服务器[0, 1] = "129.6.15.28";
            时间服务器[1, 0] = "time-b.nist.gov";
            时间服务器[1, 1] = "129.6.15.29";
            时间服务器[2, 0] = "time-a.timefreq.bldrdoc.gov";
            时间服务器[2, 1] = "132.163.4.101";
            时间服务器[3, 0] = "time-b.timefreq.bldrdoc.gov";
            时间服务器[3, 1] = "132.163.4.102";
            时间服务器[4, 0] = "time-c.timefreq.bldrdoc.gov";
            时间服务器[4, 1] = "132.163.4.103";
            时间服务器[5, 0] = "utcnist.colorado.edu";
            时间服务器[5, 1] = "128.138.140.44";
            时间服务器[6, 0] = "time.nist.gov";
            时间服务器[6, 1] = "192.43.244.18";
            时间服务器[7, 0] = "time-nw.nist.gov";
            时间服务器[7, 1] = "131.107.1.10";
            时间服务器[8, 0] = "nist1.symmetricom.com";
            时间服务器[8, 1] = "69.25.96.13";
            时间服务器[9, 0] = "nist1-dc.glassey.com";
            时间服务器[9, 1] = "216.200.93.8";
            时间服务器[10, 0] = "nist1-ny.glassey.com";
            时间服务器[10, 1] = "208.184.49.9";
            时间服务器[11, 0] = "nist1-sj.glassey.com";
            时间服务器[11, 1] = "207.126.98.204";
            时间服务器[12, 0] = "nist1.aol-ca.truetime.com";
            时间服务器[12, 1] = "207.200.81.113";
            时间服务器[13, 0] = "nist1.aol-va.truetime.com";
            时间服务器[13, 1] = "64.236.96.53";
            int portNum = 13;
            string hostName;
            byte[] bytes = new byte[1024];
            int bytesRead = 0;
            System.Net.Sockets.TcpClient client = new System.Net.Sockets.TcpClient();
            for (int i = 0; i < 13; i++)
            {
                hostName = 时间服务器[搜索顺序[i], 1];
                try
                {
                    client.Connect(hostName, portNum);
                    System.Net.Sockets.NetworkStream ns = client.GetStream();
                    bytesRead = ns.Read(bytes, 0, bytes.Length);
                    client.Close();
                    break;
                }
                catch (System.Exception)
                {
                }
            }
            char[] sp = new char[1];
            sp[0] = ' ';
            System.DateTime dt = new DateTime();
            string str1;
            str1 = System.Text.Encoding.ASCII.GetString(bytes, 0, bytesRead);

            string[] s;
            s = str1.Split(sp);
            if (s.Length < 3)
            {
                throw new Exception();
            }
            dt = System.DateTime.Parse(s[1] + " " + s[2]);//得到标准时间
            dt = dt.AddHours(8);//得到北京时间*/
            return dt;

        }

        public void Bindalldgv(DataGridView dg_admin, DataGridView dg_user, DataGridView dg_export, DataGridView dg_activity, DataGridView dg_realse)
        {
            
            string sql1 = "select UserName as '用户名',MobilePhone as '手机号',QQ as 'QQ号',RegistrationTime as '注册时间' from bm_User where Permissions=N'1' order by id desc";
            dg_admin.DataSource = sqlSelect(sql1);
            string sql2 = "select UserName as '用户名',MobilePhone as '手机号',QQ as 'QQ号',Suspended as '是否交费',Lasttime as '上次交费时间',Expirationtime as '到期时间',RegistrationTime as '注册时间',Referees as '推荐人' from bm_User where Permissions=N'0' order by id desc";
            dg_user.DataSource = sqlSelect(sql2);
            string sqlexport = "select UserName as '用户名',UserType as '用户类型',RealName as '联系人',Sex as '性别',IdentityID as '身份证号',CompanyName as '公司名称',CompanyAddress as '公司地址',CompanyProfile as '公司简介',CorporateLAN as '公司专线',Province as '所在省',City as '所在市',County as '所在县',MobilePhone as '手机号',QQ as 'QQ号',FixedTelephone as '固定电话',Fax as '传真',Email as '邮箱',Suspended as '是否交费',Lasttime as '上次交费时间',Expirationtime as '到期时间',RegistrationTime as '注册时间',Referees as '推荐人' from bm_User where Permissions=N'0' order by id desc";
            dg_export.DataSource = sqlSelect(sqlexport);
            string sql3 = "select id as '编号',theme as '活动主题',[Content] as '活动内容',Time as '发布时间' from bm_Activity order by id desc";
            dg_activity.DataSource = sqlSelect(sql3);
            string sql4 = "select UserName as '用户名',UserType as '用户类型',RealName as '联系人',Sex as '性别',IdentityID as '身份证号',CompanyName as '公司名称',CompanyAddress as '公司地址',CompanyProfile as '公司简介',CorporateLAN as '公司专线',Province as '所在省',City as '所在市',County as '所在县',MobilePhone as '手机号',QQ as 'QQ号',FixedTelephone as '固定电话',Fax as '传真',Email as '邮箱',Suspended as '是否交费',Lasttime as '上次交费时间',Expirationtime as '到期时间',RegistrationTime as '注册时间',Referees as '推荐人' from bm_User where Permissions=N'0' order by id desc";
            dg_realse.DataSource = sqlSelect(sql4);
        }

    }
}
